package com.erpanderp.wam.usm.model.service.user;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.hibernate.FlushMode;
import org.hibernate.Query;
import org.hibernate.Session;

import com.erpanderp.wam.usm.model.entity.user.Employee;
import com.erpanderp.wam.usm.model.entity.user.EmployeeAndReporting;
import com.erpanderp.wam.usm.model.entity.user.EmployeeRole;
import com.erpanderp.wam.util.CommonLookup;
import com.erpanderp.wam.util.HibernateUtil;

public class EmployeeAndReportingDAO {
	
	
	
	public List<EmployeeAndReporting> getAllEmployeesAndReportings() { 
		List<EmployeeAndReporting> allEmployeesAndReportings = null;
		try {
			Session session = HibernateUtil.getSessionFactory().getCurrentSession();
			session.beginTransaction();
			session.setFlushMode(FlushMode.COMMIT);
			Query query = session.createQuery("from EmployeeAndReporting er");
			query.setCacheable(false);
			allEmployeesAndReportings = (List<EmployeeAndReporting>) query.list();
			session.getTransaction().commit();

		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		return allEmployeesAndReportings;
	}

	
	

	public EmployeeAndReporting getEmployeeReportingDetail(Long employeeId){
		EmployeeAndReporting employeeAndReporting;		
		try{
			Session session = HibernateUtil.getSessionFactory().getCurrentSession();
			session.beginTransaction();
			session.setFlushMode(FlushMode.COMMIT);
			String queryString = "from EmployeeAndReporting er where er.employee.employeeId = :employeeId";
			
			Query query = session.createQuery(queryString).setLong("employeeId", employeeId);			
			List<EmployeeAndReporting> tempEmployeeAndReporting = (List<EmployeeAndReporting>) query.list();
			session.getTransaction().commit();
			
			if(tempEmployeeAndReporting != null && tempEmployeeAndReporting.size() > 0){
				employeeAndReporting = tempEmployeeAndReporting.get(0);
				return employeeAndReporting;
			}else{
				return null;
			}
			
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}	
	}
	
	
	
	public Employee getCenterHeadByRole(){
		Employee employee = null;
		String chRole = CommonLookup.STANDARD_ROLE_CH;
		
		
		try {
			Session session = HibernateUtil.getSessionFactory().getCurrentSession();
			session.beginTransaction();
			session.setFlushMode(FlushMode.COMMIT);
			String queryString ="select er.employee from EmployeeRole er where er.role.role in(:chRole)";
			Query query = session.createQuery(queryString).setString("chRole", chRole);
				
			List<Employee> temp = (List<Employee>) query.list();
			
			session.getTransaction().commit();
			
			if(temp != null && temp.size() > 0){
				return temp.get(0);
			}
			return null;

		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		
	}
	
	
	
	//...........
    public EmployeeAndReporting getCenterHeadByEmployeeId(Long employeeId){ 
    	EmployeeAndReporting employee ;		
			
			try{
				Session session = HibernateUtil.getSessionFactory().getCurrentSession();
				session.beginTransaction();
				session.setFlushMode(FlushMode.COMMIT);
				String queryString = "from EmployeeAndReporting er where er.employee.employeeId = :employeeId";
				
				Query query = session.createQuery(queryString);
				query.setCacheable(false).setLong("employeeId", employeeId);			
				employee = (EmployeeAndReporting) query.uniqueResult();
				
				session.getTransaction().commit();
				
				return employee;
				
			} catch (Exception e) {
				e.printStackTrace();
				return null;
			}	
		}
    public Employee getEmployeeByDesignation(String designation){ 
    		try{
				Session session = HibernateUtil.getSessionFactory().getCurrentSession();
				session.beginTransaction();
				session.setFlushMode(FlushMode.COMMIT);
				String queryString = "from Employee e where e.designation =:designation";
				
				Query query = session.createQuery(queryString).setString("designation",designation);
							
				List<Employee> temp = (List<Employee>) query.list();
				
				session.getTransaction().commit();
				
				if(temp != null && temp.size() > 0){
					return temp.get(0);
				}
				return null;
				
			} catch (Exception e) {
				e.printStackTrace();
				return null;
			}	
		}
    public Employee getAccountantByModule(String moduleName){ 
    	Employee employee = null;
    	
			
			try{
				Session session = HibernateUtil.getSessionFactory().getCurrentSession();
				session.beginTransaction();
				session.setFlushMode(FlushMode.COMMIT);
				String queryString = "from Employee e where e.moduleName =:moduleName";
				
				Query query = session.createQuery(queryString).setString("moduleName",moduleName);
			
				List<Employee> tempEmployee = (List<Employee>) query.list();
				session.getTransaction().commit();
				
				if(tempEmployee != null && tempEmployee.size() > 0){
					employee = tempEmployee.get(0);
					return employee;
				}else{
					return null;
				}
				
			} catch (Exception e) {
				e.printStackTrace();
				return null;
			}	
		}
    //Get Competency Employee List    
	public List<EmployeeAndReporting> getAllEmployeesByCompetency(String employeeCode) { 
		List<EmployeeAndReporting> allEmployees = null;
		try {
			Session session = HibernateUtil.getSessionFactory().getCurrentSession();
			session.beginTransaction();
			session.setFlushMode(FlushMode.COMMIT);
			Query query = session.createQuery("from EmployeeAndReporting er where er.competencelead.employeeCode =:employeeCode)");
			query.setCacheable(false).setString("employeeCode", employeeCode);
			allEmployees = (List<EmployeeAndReporting>) query.list();
			session.getTransaction().commit();

		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		return allEmployees;
	}
	public List<EmployeeAndReporting> getAllEmployeesUnderReporting(String employeeCode) { 
		List<EmployeeAndReporting> allEmployees = null;
		try {
			Session session = HibernateUtil.getSessionFactory().getCurrentSession();
			session.beginTransaction();
			session.setFlushMode(FlushMode.COMMIT);
			Query query = session.createQuery("from EmployeeAndReporting er where er.projectManager.employeeCode =:employeeCode)");
			query.setCacheable(false).setString("employeeCode", employeeCode);
			allEmployees = (List<EmployeeAndReporting>) query.list();
			session.getTransaction().commit();

		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		return allEmployees;
	}
	
	public List<Employee> getAllLeadEmployeesList() { 
		List<Employee> employeeLeadsList = null;
		String chRole = CommonLookup.STANDARD_ROLE_CH;
		String vpRole = CommonLookup.STANDARD_ROLE_VP;
		String pmRole = CommonLookup.STANDARD_ROLE_PROJECT_MANAGER;
		String clRole = CommonLookup.STANDARD_ROLE_COMPETENCY_LEAD;
		String cmdRole = CommonLookup.STANDARD_ROLE_CMD;
		String cooRole = CommonLookup.STANDARD_ROLE_COO;
		try {
			Session session = HibernateUtil.getSessionFactory().getCurrentSession();
			session.beginTransaction();
			session.setFlushMode(FlushMode.COMMIT);
			String queryString ="select er.employee from EmployeeRole er where er.role.role in(:chRole,:vpRole,:pmRole,:clRole, :cmdRole, :cooRole) and er.employee.status = :employeeStatus";
			Query query = session.createQuery(queryString).setString("chRole", chRole).setString("vpRole", vpRole).setString("pmRole", pmRole).setString("clRole", clRole).setString("cmdRole", cmdRole).setString("cooRole", cooRole).setString("employeeStatus", CommonLookup.STATUS_ACTIVE);
			
			employeeLeadsList = (List<Employee>) query.list();
			session.getTransaction().commit();

		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		return employeeLeadsList;
	}
	
	public List<Employee> getCenterHeadsList() { 
		List<Employee> employeeLeadsList = null;
		String chRole = CommonLookup.STANDARD_ROLE_CH;
		
		
		try {
			Session session = HibernateUtil.getSessionFactory().getCurrentSession();
			session.beginTransaction();
			session.setFlushMode(FlushMode.COMMIT);
			String queryString ="select er.employee from EmployeeRole er where er.role.role in(:chRole) and er.employee.status = :employeeStatus";
			Query query = session.createQuery(queryString).setString("chRole", chRole).setString("employeeStatus", CommonLookup.STATUS_ACTIVE);
			
			employeeLeadsList = (List<Employee>) query.list();
			session.getTransaction().commit();

		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		return employeeLeadsList;
	}
	
	public EmployeeAndReporting updateEmployeeAndReporting(EmployeeAndReporting emplAndReporting){
		System.out.println("IN UPDATE EMPLOYEE AND REPORTING ");
		//System.out.println("IN UPDATE EMPLOYEE AND REPORTING PROJECT MANAGER ="+emplAndReporting.getProjectManager().getFirstName());
		try{
		 Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		 session.beginTransaction();
		 session.setFlushMode(FlushMode.COMMIT);
		 session.saveOrUpdate(emplAndReporting);
		 session.getTransaction().commit();
		 return emplAndReporting;	
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
	}
	
	/*//Get Competency Employee Leaves List    
		public List<EmployeeAndReporting> getAllLeavesByCompetency(String employeeCode,String selectDate) { 
			List<EmployeeAndReporting> allEmployeeLeaves = null;
			try {
				Session session = HibernateUtil.getSessionFactory().getCurrentSession();
				session.beginTransaction();
				session.setFlushMode(FlushMode.COMMIT);
				//SELECT * FROM resource_util_master t1 LEFT OUTER JOIN leave_table t2 ON t1.EmpId = t2.EmpId where t1.Cl_Id='"+emp_code+"' and '"+dateNow+"' between Leavefrom and Leaveto 
				Query query = session.createQuery("from EmployeeAndReporting er LEFT OUTER JOIN EmployeeLeave el ON er.employee.employeeCode = el.employee.employeeCode where er.competencelead.employeeCode =:employeeCode and '' between el.periodFrom and el.periodTo)");
				query.setCacheable(false).setString("employeeCode", employeeCode).setString("selectDate", selectDate);
				allEmployeeLeaves = (List<EmployeeAndReporting>) query.list();
				session.getTransaction().commit();

			} catch (Exception e) {
				e.printStackTrace();
				return null;
			}
			return allEmployeeLeaves;
		}*/

	public List<Employee> getTimesheetApprovers(Long employeeId){
		List<Employee> approvers = new ArrayList<Employee>();
		
		try {
			Session session = HibernateUtil.getSessionFactory().getCurrentSession();
			session.beginTransaction();
			session.setFlushMode(FlushMode.COMMIT);
			String queryString ="select er.competencelead from EmployeeAndReporting er where" +
					" er.employee.employeeId = :employeeId and er.competencelead.status = :status";
			Query query = session.createQuery(queryString).setLong("employeeId", employeeId).setString("status", CommonLookup.STATUS_ACTIVE);			
			List<Employee> temp = (List<Employee>) query.list();			
			approvers.addAll(temp);
			
			queryString ="select er.projectManager from EmployeeAndReporting er where" +
					" er.employee.employeeId = :employeeId and er.projectManager.status = :status";
			query = session.createQuery(queryString).setLong("employeeId", employeeId).setString("status", CommonLookup.STATUS_ACTIVE);			
			temp = (List<Employee>) query.list();			
			approvers.addAll(temp);
			
			queryString ="select er.centerHead from EmployeeAndReporting er where" +
					" er.employee.employeeId = :employeeId and er.centerHead.status = :status";
			query = session.createQuery(queryString).setLong("employeeId", employeeId).setString("status", CommonLookup.STATUS_ACTIVE);			
			temp = (List<Employee>) query.list();			
			approvers.addAll(temp);
			
			queryString ="select er.employee from EmployeeRole er where" +
					" er.role.role = :role and er.employee.status = :status";
			query = session.createQuery(queryString).setString("role", "HR").setString("status", CommonLookup.STATUS_ACTIVE);			
			temp = (List<Employee>) query.list();			
			approvers.addAll(temp);
			
			session.getTransaction().commit();

		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		return approvers;
	}
	
	public static void main(String a[]){
		System.out.println("=====77==7==="+new EmployeeAndReportingDAO().getCenterHeadByEmployeeId(1l));
		System.out.println(new EmployeeAndReportingDAO().getEmployeeReportingDetail(133l));
		List<Employee> approvers=new EmployeeAndReportingDAO().getTimesheetApprovers(10l); 
		for(Employee employee : approvers){
			System.out.println(employee.getFirstName());
		}
	}
}
